/*
* CREATE ROLE
*/
Oid
CreateRole(
ParseState *pstate,
CreateRoleStmt *stmt
)
{
[...]
/* The defaults can vary depending on the original
* statement type */
switch (stmt->stmt_type)
{
case ROLESTMT_ROLE:
break;
case ROLESTMT_USER:
canlogin = true;
/* may eventually want inherit to default to
* false here */
break;
case ROLESTMT_GROUP:
break;
}
[...]
}
In PostgreSQL versions before 8.1, users and groups
were distinct kinds of entities, but now there are
only roles. Any role can act as a user, a group, or
both.
laetitia=> create role Beatrice_Worsley with login;
CREATE ROLE
laetitia=> create table test(
id integer generated always as identity,
value text);
CREATE TABLE
laetitia=> alter table test owner to beatrice_worsley ;
ALTER TABLE
laetitia=> set role beatrice_worsley ;
SET
laetitia=> insert into test(value)
select 'Serial Programming for Real and Idealized Digital Calculating Machines';
INSERT 0 1
laetitia=> table test \gx
-[ RECORD 1 ]-----------------------------------------------------------------
id | 1
value | Serial Programming for Real and Idealized Digital Calculating Machines
laetitia=> delete from test;
DELETE 1
laetitia=> create role Klára_Dán_von_Neumann with login;
CREATE ROLE
laetitia=> set role Klára_Dán_von_Neumann;
SET
laetitia=> create table test(
id integer generated always as identity,
value text);
ERROR: permission denied for schema public
LINE 1: create table test(id integer generated always as identity, v...
^
laetitia=> grant create on schema public to Klára_Dán_von_Neumann;
GRANT
laetitia=> set role Klára_Dán_von_Neumann;
SET
laetitia=> create table test(
id integer generated always as identity,
value text);
CREATE TABLE
laetitia=> create table test(
id integer generated always as identity,
value text);
CREATE TABLE
laetitia=> alter table test owner to beatrice_worsley ;
ALTER TABLE
laetitia=> set role beatrice_worsley ;
SET
laetitia=> grant insert on table test to klára_dán_von_neumann;
GRANT
laetitia=> set role klára_dán_von_neumann;
SET
laetitia=> insert into test(value)
values('The Computer and the Brain');
INSERT 0 1
laetitia=> select * from test;
ERROR: permission denied for table test
laetitia=> create table test(id integer, value text);
CREATE TABLE
laetitia=> create sequence test_seq;
CREATE SEQUENCE
laetitia=> alter table test owner to beatrice_worsley ;
ALTER TABLE
laetitia=> set role beatrice_worsley ;
SET
laetitia=> insert into test(id, value)
select nextval('test_seq'),
'Serial Programming for Real and Idealized Digital Calculating Machines';
ERROR: permission denied for sequence test_seq
laetitia=> create schema Joyce_Currie_Little;
CREATE SCHEMA
laetitia=> create role readonly;
CREATE ROLE
laetitia=> grant usage
on schema Joyce_Currie_Little
to readonly;
GRANT
laetitia=> grant select
on all tables in schema Joyce_Currie_Little
to readonly;
GRANT
laetitia=> alter default privileges
in schema Joyce_Currie_Little
grant select on tables to readonly;
ALTER DEFAULT PRIVILEGES
laetitia=> create table Joyce_Currie_Little.test(
id integer,
value text) ;
CREATE TABLE
laetitia=> create role Gwen_Bell with login;
CREATE ROLE
laetitia=> grant readonly to Gwen_Bell ;
GRANT ROLE
laetitia=> set role gwen_bell ;
SET
laetitia=> select * from Joyce_Currie_Little.test;
id | value
----+-------
(0 rows)
laetitia=> set role laetitia;
SET
laetitia=> revoke readonly from gwen_bell ;
REVOKE ROLE
laetitia=> set role gwen_bell ;
SET
laetitia=> select * from Joyce_Currie_Little.test;
ERROR: permission denied for schema joyce_currie_little
LINE 1: select * from Joyce_Currie_Little.test;
^
laetitia=> set role laetitia;
SET
laetitia=> grant usage
on schema Joyce_Currie_Little
to gwen_bell ;
GRANT
laetitia=> set role gwen_bell ;
SET
laetitia=> select * from Joyce_Currie_Little.test;
ERROR: permission denied for table test
laetitia=> create role Cathy_Marshall with login;
CREATE ROLE
laetitia=> create role Sarah_Allen with login;
CREATE ROLE
laetitia=> alter default privileges in schema public
grant select on tables to sarah_allen ;
ALTER DEFAULT PRIVILEGES
laetitia=> alter schema public owner to cathy_marshall ;
ALTER SCHEMA
laetitia=> set role cathy_marshall ;
SET
laetitia=> create table test (id integer, value text);
CREATE TABLE
laetitia=> set role sarah_allen ;
SET
laetitia=> table test;
ERROR: permission denied for table test
laetitia=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
----------+---------------+-------+------------------------
laetitia | public | table | sarah_allen=r/laetitia
(1 row)
laetitia=> set role laetitia;
SET
laetitia=> drop table test;
DROP TABLE
laetitia=> alter default privileges
for role cathy_marshall in schema public
grant select on tables to sarah_allen ;
ALTER DEFAULT PRIVILEGES
laetitia=> create table test (id integer, value text);
CREATE TABLE
While you can change your own default privileges and
the defaults of roles that you are a member of, at
object creation time, new object permissions are only
affected by the default privileges of the current
role, and are not inherited from any roles in which
the current role is a member.
laetitia=> create table systers
(admin text, member text, email_address text);
CREATE TABLE
laetitia=> create role member;
CREATE ROLE
laetitia=> create role admin;
CREATE ROLE
laetitia=> create role anita_borg with login;
CREATE ROLE
laetitia=> create role robin_jeffries with login;
CREATE ROLE
laetitia=> grant select on systers to admin, member;
GRANT
laetitia=> alter table systers enable row level security;
ALTER TABLE
laetitia=> create policy systers_admin on systers
to admin using (admin = current_user);
CREATE POLICY
laetitia=> create policy systers_member on systers
to member using (member = current_user);
CREATE POLICY
laetitia=> set role robin_jeffries;
SET
laetitia=> select * from systers;
admin | member | email_address
------------+----------------+-----------------------------
anita_borg | robin_jeffries | robin.jeffries@mexample.com
(1 row)